Water Timeline Export - Documentation
This document explains the fields present in the water timeline export CSV output for Group Link Water.
Example file:
events.DEFAULT..2024-04-25T11_00_00.000Z.csv
Output Fields:
| Field Name | Description | Caveats |
|---|---|---|
| device_id | Unique identifier of the water meter device. | |
| channel | Channel number of the reading (relevant for multi-channel devices). | May be dropped if the organization doesn't have multi-channel devices. |
| city | City where the device is located. | |
| remote_id | Remote identifier of the device, specific to the organization. | |
| reading | The actual water meter reading in cubic meters (m³), calculated using the formula: multiplier * (raw_pulses - discount_offset) + result_offset | The formula components are explained in the following rows. |
| raw_pulses_delta | Difference in raw pulse count between the current and previous device reading. | It's calculated using the LOCF (Last Observed Carried Forward) value. For the first file, it’s considered zero when no value is found. |
| reading_date | Timestamp of first data acquisition. | |
| reading_time | Timestamp of the reading on grouplink server (truncated to the minute). | |
| latency_max | Maximum latency observed for the reading, representing the time difference between the reading time and the receiving time. | |
| latency_min | Minimum latency observed for the reading, similar to latency_max. | |
| records | Number of records aggregated for this specific reading (due to grouping by minute). | |
| multiplier | Multiplier used in the reading calculation, specific to the device and channel. | Obtained from device activation data and offset information. May change during device life-time when device is decommissioned and activated again. |
| raw_pulses | Raw pulse count from the device for this reading. | |
| discount_offset | Offset value used in the reading calculation, specific to the device and channel. | Obtained from device activation data and offset information. May change during device life-time when device is decommissioned and activated again. |
| result_offset | Result offset used in the reading calculation, specific to the device and channel. | Obtained from device activation data and offset information. May change during device life-time when device is decommissioned and activated again. |
Caveats and Additional Information
- The reading value is calculated using a formula that involves the multiplier, raw_pulses, discount_offset, and result_offset. These values are specific to the device and channel and are obtained from device activation data and offset information.
- The latency_max and latency_min fields represent the time difference between the event time (when the reading was taken) and the receiving time (when the reading was transmitted).
- The reading values are expressed in cubic meters (m³).
Importing into Another System
When importing this data into another system, it's crucial to consider the following:
- Ensure the data types of each field are correctly mapped in the target system. Pay close attention to decimal precision for fields like reading, multiplier, and result_offset.
- Ensure correct interpretation and conversion of timestamps, considering time zones and potential daylight saving time adjustments. All data is in UTC.
- The latency information can be valuable for analyzing data transmission delays and potential issues with device communication.
Suggested Data Types for Importing
This table provides suggested data types for importing the water timeline export CSV data into another system:
| Output Field | Our Data Type | Suggested Data Type | Notes |
|---|---|---|---|
| device_id | UINTEGER | Integer (unsigned) | Large integer type for storing device IDs. |
| channel | TINYINT | Integer (smallint, tinyint) | Small integer type for storing channel numbers. |
| city | VARCHAR | String (text, varchar) | Variable-length string to accommodate city names. |
| remote_id | VARCHAR | String (text, varchar) | Variable-length string for remote identifiers. |
| reading | DECIMAL(16, 6) | Decimal (numeric with precision) | Decimal type with sufficient precision for water meter readings. |
| raw_pulses_delta | BIGINT | Integer (bigint) | Large integer type for storing raw pulse differences. |
| reading_date | TIMESTAMPTZ | Date/Time (with time zone) | Date and time type with time zone information. |
| reading_time | TIMESTAMPTZ | Date/Time (with time zone) | Date and time type with time zone information. |
| latency_max | INTERVAL | Interval (duration) | Data type representing a duration of time. Exported as seconds in hh:mm:ss.ms |
| latency_min | INTERVAL | Interval (duration) | Data type representing a duration of time. Exported as seconds in hh:mm:ss.ms |
| records | INT | Integer | Integer type for storing the number of aggregated records. |
| multiplier | DECIMAL(10, 6) | Decimal (numeric with precision) | Decimal type for storing the multiplier value. |
| raw_pulses | BIGINT | Integer (bigint) | Large integer type for raw pulse counts. |
| discount_offset | BIGINT | Integer (bigint) | Large integer type for the discount offset value. |
| result_offset | DECIMAL(16, 6) | Decimal (numeric with precision) | Decimal type for the result offset value. |